{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "75417ef5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import Series, DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2dbd4a89",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "filename = '../data/nyc-parking-violations-2020.csv'\n",
    "df = pd.read_csv(filename,\n",
    "                usecols=['Plate ID', \n",
    "                         'Registration State',\n",
    "                         'Plate Type',\n",
    "                         'Feet From Curb',\n",
    "                        'Vehicle Make',\n",
    "                         'Vehicle Color'])\n",
    "df.columns = ['pid', 'state', 'ptype', 'make', 'color', 'feet']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "06f37257",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.2 s ± 420 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find all of the cars whose registration state is from New York, New Jersey, or Connecticut, using .loc.\n",
    "# How long does it take to perform this query?\n",
    "%timeit df.loc[(df['state'] == 'NY') | (df['state'] == 'NJ') |  (df['state'] == 'CT')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "795327cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.49 s ± 69.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find all of the cars whose registration state is from New York, New Jersey, or Connecticut, using df.query.\n",
    "# How long does it take to perform this query?\n",
    "%timeit df.query(\"state == 'NY' or state == 'NJ' or state == 'CT'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "39f857a6",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5597826086956522"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How much faster was it to use \"query\"?\n",
    "1.03 / 1.84"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7ef43f13",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "838 ms ± 28.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Use \"isin\" to search for the states.  How does this technique compare?\n",
    "%timeit df.loc[df['state'].isin(['NY', 'NJ', 'CT'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e6879940",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "854 ms ± 14.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df.query('state.isin([\"NY\", \"NJ\", \"CT\"])')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "eb78522e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "924 ms ± 14.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York using df.loc, and time it\n",
    "%timeit df.loc[(df['state'] == 'NY')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fc64d416",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "799 ms ± 13.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York using df.query, and time it\n",
    "%timeit df.query('state == \"NY\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8869dfcb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "775 ms ± 22.6 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York using df.eval, and time it\n",
    "%timeit df[df.eval('state == \"NY\"')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b41e15dc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "771 ms ± 19.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "%timeit df.loc[df.eval('state == \"NY\"')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "823f8cd5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.27 s ± 10.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates using df.loc, and time it\n",
    "%timeit df.loc[((df['state'] == 'NY') & (df['ptype'] == 'PAS'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "36c90724",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "965 ms ± 58.7 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates using df.query, and time it\n",
    "%timeit df.query('state == \"NY\" & ptype == \"PAS\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8f7231b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "924 ms ± 16.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates using df.eval\n",
    "%timeit df.loc[df.eval('state == \"NY\" & ptype == \"PAS\"')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "cdd2532c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.34 s ± 14.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color using df.loc, and time it\n",
    "%timeit df.loc[((df['state'] == 'NY') & (df['ptype'] == 'PAS') & (df['color'] == 'WHITE'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "1fd76d4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "728 ms ± 3.51 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color using df.query, and time it\n",
    "%timeit df.query('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e50a336f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "727 ms ± 2.05 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color  using df.eval\n",
    "%timeit df.loc[df.eval('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\"')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "02d492f0",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.31 s ± 3.62 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color parked > 1 foot from the curb using df.loc, and time it\n",
    "%timeit df.loc[((df['state'] == 'NY') & (df['ptype'] == 'PAS') & (df['color'] == 'WHITE') & (df['feet'] > 1))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5e93b317",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "712 ms ± 5.88 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color  parked > 1 foot from the curb  using df.query, and time it\n",
    "%timeit df.query('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\" & feet > 1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "554b3f21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "706 ms ± 3.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars from New York with PAS plates with WHITE color  parked > 1 foot from the curb using df.eval\n",
    "%timeit df.loc[df.eval('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\" & feet > 1')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "296bc0a5",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1.75 s ± 4.85 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars with make TOYOT from New York with PAS plates with WHITE color parked > 1 foot from the curb using df.loc, and time it\n",
    "%timeit df.loc[((df['state'] == 'NY') & (df['ptype'] == 'PAS') & (df['color'] == 'WHITE') & (df['feet'] > 1) & (df['make'] == 'TOYOT'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "53066f4f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "896 ms ± 2.98 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Find cars with make TOYOT  from New York with PAS plates with WHITE color  parked > 1 foot from the curb  using df.query, and time it\n",
    "%timeit df.query('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\" & feet > 1 & make == \"TOYOT\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c914eff4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "899 ms ± 4.33 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)\n"
     ]
    }
   ],
   "source": [
    "# Fin d cars with make TOYOT  from New York with PAS plates with WHITE color  parked > 1 foot from the curb  using df.eval\n",
    "%timeit df.loc[df.eval('state == \"NY\" & ptype == \"PAS\" & color == \"WHITE\" & feet > 1 & make == \"TOYOT\"')]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
